<?php

function add_employee($name, $typeid, $phone, $address, $email)
{
    $sql = "INSERT INTO " . TB_PREF .
        "employees (empname, typeid, phone, address, email) values (" . db_escape($name) .
        "," . db_escape($typeid) . "," . db_escape($phone) . "," . db_escape($address) .
        "," . db_escape($email) . ")";
    db_query($sql, "The employee could not be added");
}

function update_employee($id, $name, $typeid, $phone, $address, $email)
{
    $sql = "UPDATE " . TB_PREF . "employees SET empname=" . db_escape($name) . ", 
		typeid=" . db_escape($typeid) . ",
		phone=" . db_escape($phone) . ",
		address=" . db_escape($address) . ",
		email=" . db_escape($email) . "
		WHERE empid = " . db_escape($id);

    db_query($sql, "The employee could not be updated");
}

function delete_employee($id)
{
    $sql = "DELETE FROM " . TB_PREF . "employees WHERE empid=" . db_escape($id);
    ;
    db_query($sql, "cannot delete employee");
}

function get_employees($show_inactive)
{
    $sql = "SELECT * FROM " . TB_PREF .
        "employees_type INNER JOIN employees ON (employees_type.typeid = employees.typeid)";
    if (!$show_inactive)
        $sql .= " WHERE !inactive";
    return db_query($sql, "could not get employees");
}

function get_employee($selected_id)
{
    $sql = "SELECT *  FROM " . TB_PREF . "employees WHERE empid=" . db_escape($selected_id);

    $result = db_query($sql, "could not get employee");
    return db_fetch($result);
}

function get_list_pittycash_detail()
{
    $sql = "select * from " . TB_PREF .
        "pittycash_details where pittycashid = (SELECT pittycashid from " . TB_PREF .
        "pittycash WHERE !inactive)";
    return db_query($sql, "could not get pitty cash details");

}

function get_setting_kas_kecil()
{
    $sql = "select value from " . TB_PREF . "settings where name = " . db_escape('kuota_kaskecil');
    $result = db_query($sql, "could not get settings kas kecil.");
    return db_result($result);

}

function get_bank_balance($bank_acc)
{
    $to = add_days(Today(), 1);
    $bal = get_balance_before_for_bank_account($bank_acc, $to);
    return $bal;
}

function save_setting_kas_kecil($value)
{
    $sql = "update " . TB_PREF . "settings set value = " . db_escape($value) .
        " where setid = 1";
    db_query($sql, "could not get settings kas kecil.");

}

function save_pengisian_kas_kecil($ref)
{
    global $Refs;
    begin_transaction();
    $id_pittycash = get_kaskecil();

    $sql_update_pittycash = "update " . TB_PREF . "pittycash set ref = " . db_escape($ref) .
        "
	,tglapp=" . db_escape(date2sql(Today())) . "
	,inactive=1 where pittycashid = " . db_escape($id_pittycash['pittycashid']);
    db_query($sql_update_pittycash);

    //add_bank_trans(ST_RETKASKECIL, $id_pittycash['pittycashid'], $bankacc, $ref,
    //        date2sql(Today()), $total, null, null, "", "");
    $sql_get_id = "select id from kasbon where kasbon.status and not kasbon.status_journal";
    $result = db_query($sql_get_id);
    $sql_insert_detil_pittycash = "insert into " . TB_PREF .
        "pittycash_details (pittycashid,id) values ";
    while ($id = db_fetch($result))
    {
        $sql_insert_detil_pittycash .= "(" . db_escape($sql_get_id['pittycashid']) . "," .
            db_escape($id) . ")";
    }
    db_query($sql_insert_detil_pittycash);

    $Refs->save(ST_RETKASKECIL, $id_pittycash['pittycashid'], $ref);
    commit_transaction();
}

function get_kaskecil_id_from_ref($ref)
{
    $sql = "select pittycashid from pittycash where ref = ".db_escape($ref);
    return db_result(db_query($sql));
}

function create_kas_kecil($from_details, $ref)
{
    begin_transaction();
    
    $total_amount = get_setting_kas_kecil();
    
    if($ref != 'Auto')
        $id_kaskecil = get_kaskecil_id_from_ref($ref);
    else
        $id_kaskecil = -1;
        
    $sql_create_kaskecil = "INSERT INTO " . TB_PREF . "pittycash 
	(tglcreate, total, inactive, quota, quota_sisa) 
	VALUES (" . db_escape(date2sql(Today())) . ",
	0,0," . db_escape($total_amount) . "," . db_escape($total_amount) . ")";
    db_query($sql_create_kaskecil);
    // TODO: add bank tranfer, buat ref
    $sql_pittycash = "insert into " . TB_PREF .
        "pittycash_bank (pittycashid,tgl,trans_no,ref,bank_id,no_slip) values ";   
    foreach ($from_details as $detils)
    {
        $Refs = new references();
        $next_ref = $Refs->get_next(ST_BANKTRANSFER);
        $trans_no = add_bank_transfer($detils['bank_id'], '2', $detils['date_'], $detils['amount'],
            $next_ref, 'Pembentukan Kas Kecil');
            
        $sql_pittycash .= "(" . db_escape($id_kaskecil) . "," .                    
                    db_escape(date2sql($detils['date_'])) . "," .
                    db_escape($trans_no) . "," .
                    db_escape($ref) . "," .
                    db_escape($detils['bank_id']) . "," .
                    db_escape($detils['no_slip']) .
                    ")";            
    }
    
    db_query($sql_pittycash);
    
    commit_transaction();
}

function get_kas_bon($kasbon_id)
{
    $sql = "SELECT kasbon.*, employees.empname FROM " . TB_PREF .
        "kasbon INNER JOIN " . TB_PREF .
        "employees ON (kasbon.employee_id = employees.empid) where id = " . db_escape($kasbon_id);
    $result = db_query($sql, "could not get kas bon.");
    return db_fetch($result);
}

function get_kas_bon_details($kasbon_id)
{
    $sql = "SELECT id,no_note,acc_id,note,amount FROM " . TB_PREF .
        "kasbon_details where kasbon_id = " . db_escape($kasbon_id);
    $result = db_query($sql, "could not get kas bon details.");
    return $result;
}

function del_kas_bon_details($kasbon_id, $detail_id)
{
    $sql = "Delete FROM " . TB_PREF . "kasbon_details where kasbon_id = " .
        db_escape($kasbon_id) . " and id = " . db_escape($detail_id);
    db_query($sql, "could not get kas bon details.");

}

function get_kaskecil()
{
    //update_pittycash_quota();
    $sql = "SELECT * FROM " . TB_PREF . "pittycash where not inactive";
    $result = db_query($sql, "could not get kas kecil.");
    $num = db_num_rows($result);
    if ($num > 1)
    {
        display_warning("You have duplicate active pittycash");
    } else
        if ($num == 1)
        {
            return db_fetch($result);
        } else
        {
            return null;
        }
        //else
        //	display_warning("You have missing or invalid active pittycash.");

}

function update_pittycash_quota()
{
    begin_transaction();
    $arrtotal = get_total_kasbon();
    $total = $arrtotal['total'];
    $sql_total = "update pittycash set total = " . $total . ",quota_sisa = quota - " .
        $total . " where not inactive";
    db_query($sql_total);
    commit_transaction();
}

function get_total_kasbon()
{
    $sql = "select sum(amount) as total from kasbon_details where kasbon_id in
  (select kasbon.id from kasbon where status = 1 and
    status_journal = 0)";
    $result = db_query($sql);
    return db_fetch($result);
}

function get_kaskecil_detil()
{

    $sql = "SELECT 
  kasbon.ref as no_kasbon,
  kasbon_details.no_note,
  kasbon.`date`,
  kasbon_details.amount,
  kasbon_details.acc_id,
  chart_master.account_name
FROM
  " . TB_PREF . "pittycash_details
  INNER JOIN " . TB_PREF .
        "pittycash ON (pittycash_details.pittycashid = pittycash.pittycashid)
  INNER JOIN " . TB_PREF . "kasbon ON (pittycash_details.id = kasbon.id)
  INNER JOIN " . TB_PREF .
        "kasbon_details ON (kasbon.id = kasbon_details.kasbon_id)
  INNER JOIN " . TB_PREF .
        "chart_master ON (kasbon_details.acc_id = chart_master.account_code)
  WHERE not pittycash.inactive";
    return db_query($sql, "could not get kas kecil detil.");

}

function add_kas_bon($doc)
{
    $Refs = new references();
    //begin_transaction();

    $sql = "INSERT INTO " . TB_PREF .
        "kasbon (ref, date, employee_id, amount, note) values (" . db_escape($doc['ref']) .
        "," . db_escape($doc['date']) . "," . db_escape($doc['employee_id']) . "," .
        db_escape($doc['amount']) . "," . db_escape($doc['note']) . ")";
    db_query($sql, "Kas bon could not be added");

    $sql = "select id from " . TB_PREF . "kasbon where ref =" . db_escape($doc['ref']);
    $result = db_query($sql, "Kas bon id can't get.");
    $id = db_fetch($result);

    $Refs->save(ST_KASBON, $id['id'], $doc['ref']);

    //commit_transaction();
}

function is_valid_no_note($no_note)
{
    $sql = "select id from " . TB_PREF . "kasbon_details where no_note = " .
        db_escape($no_note);
    $result = db_query($sql);
    return db_num_rows($result) < 1;
}

function save_kas_bon_details($ref, $ref_kasbon, $items)
{
    global $Refs;
    begin_transaction();
    $sql = "insert into " . TB_PREF .
        "kasbon_details(kasbon_id,date,no_note,acc_id,note,amount) values";
    foreach ($items as $item)
    {
        if (!array_key_exists('id', $item))
        {
            $sql .= "(" . db_escape($ref_kasbon) . "," . db_escape(date2sql(Today())) . "," .
                db_escape($item['no_note']) . "," . db_escape($item['acc_id']) . "," . db_escape($item['note']) .
                "," . db_escape($item['amount']) . ")";
            if (false !== next($items))
                $sql .= ",";
        }
    }
    $sql = rtrim($sql, ',');

    db_query($sql, "Kas bon details could not be added");
    $sql = "update " . TB_PREF . "kasbon set status = '1',ref_pkb = '$ref' where id = " .
        db_escape($ref_kasbon);
    db_query($sql, "Kas bon could not be updated");
    //$Refs = new references();
    $Refs->save(ST_RETKASBON, $ref_kasbon, $ref);
    commit_transaction();
}

function get_kaskecil_all($from = '', $to = '', $ref = '')
{
    update_pittycash_quota();
    $date_after = date2sql($from);
    $date_before = date2sql($to);
    $sql = "SELECT pittycashid,
	ref,
	 tglcreate,
	 tglapp,
	 total 
	from " . TB_PREF . "pittycash 
	where tglcreate >= '$date_after'
			AND tglcreate <= '$date_before'
	";
    //$result = db_query($sql);
    if ($ref != "")
    {
        // search orders with reference like
        $number_like = "%" . $ref . "%";
        $sql .= " AND ref LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    }

    $sql .= " GROUP BY ref";
    return $sql;
}

function get_kasbonall($from = '', $to = '', $ref = '', $status = 0)
{
    /*$sql = "SELECT kasbon.ref as ref,kasbon.date as date, kasbon_details.acc_id, kasbon_details.amount as amount, kasbon_details.no_note as no_note from kasbon where `status` = 1";
    $result = db_query($sql);
    return $result;
    */
    $date_after = date2sql($from);
    $date_before = date2sql($to);

    $sql = "SELECT
		    kasbon.id, 
			kasbon.ref as ref,
			kasbon.note as note,
			kasbon.date as date,
			emp.empname as pemohon,
			case when kasbon.status = 1 then 'sudah' else 'belum' end as status
		FROM " . TB_PREF . "kasbon as kasbon," . TB_PREF . "employees as emp 
		    WHERE kasbon.employee_id = emp.empid
			AND kasbon.status = " . db_escape($status) . "
			AND kasbon.date >= '$date_after'
			AND kasbon.date <= '$date_before'";

    if ($ref != "")
    {
        // search orders with reference like
        $number_like = "%" . $ref . "%";
        $sql .= " AND kasbon.ref LIKE " . db_escape($number_like);
        //				." GROUP BY sorder.order_no";
    }

    $sql .= " GROUP BY kasbon.ref";
    return $sql;
}

?>